
clear
use subsidy_value, replace


*The beginning of this .do file is going to compare the annual debt service to the
*payments based on the funding source interest rate listed on the application. For below market rate loans, the subsidies
*will be calculated differently based on which group the funding source falls.

*I'll calculate the annual payment with a given interest rate by getting the monthly payment and mulitplying by 12.

replace perm_rate=0 if perm_rate==.
replace perm_annualservice=0 if perm_annualservice==.
gen oneplusratemonthly=1+(perm_rate/12)

gen imputedannualpayment=0
format %18.0g imputedannualpayment

replace imputedannualpayment=((((perm_rate/12)*RPA)*(oneplusrate^perm_term/(oneplusrate^perm_term-1))))*12
replace imputedannualpayment=(RPA/perm_term)*12 if perm_rate==0
replace imputedannualpayment=(RPA/perm_term)*12 if perm_rate==.&notloan==0

replace imputedannualpayment=((((perm_rate/12)*RPA)*(oneplusrate^360/(oneplusrate^360-1))))*12 if perm_term==.
replace imputedannualpayment=(RPA/360)*12 if perm_rate==0&perm_term==.
replace imputedannualpayment=(RPA/360)*12 if perm_rate==.&notloan==0&perm_term==.

gen diffpct=0
replace diffpct=(imputedannualpayment-(perm_annualservice*(RPA/PA)))/imputedannualpayment

gen diffpctgroup=0
replace diffpctgroup=1 if diffpct<=0.02&diffpct>-0.02
replace diffpctgroup=2 if diffpct>0.02
replace diffpctgroup=3 if diffpct<-0.02

*diffpctgroup==1 means that the two are very close
*diffpctgroup==2 means that imputed payment is greater than the annual debt service
*diffpctgroup==3 means that imputed payment is less than the annual debt service


******************************************
***Calculate the subsidy for Residual Loans
******************************************


*Maximum subsidy - RL

recode perm_annualservice (.=0)
replace perm_annualservice=perm_annualservice*-1 if perm_annualservice<0
*I also adjust for the real value here by using the RPA relative to the RA
gen monthlypayment_required=(perm_annualservice/12)*(RPA/PA)
gen marketmonthlyrate_mean=marketratemean/12
gen marketmonthlyrate_t30=treasury30/12

gen A_mean=1/(1+marketmonthlyrate_mean)
gen A_t30=1/(1+marketmonthlyrate_t30)

*Mean Market Rate
gen PV_required_mean=monthlypayment_required*(1-A_mean^perm_term)/(1-A_mean)
replace PV_required_mean=monthlypayment_required*(1-A_mean^360)/(1-A_mean) if perm_term==.
gen maxsubRL_mean=RPA-PV_required_mean if RL==1
*This is happening because the market rate is less than the rate listed on the application and
*the required annual payment is very similar or greater than the imputed payment. 
*Clearly there is very little subsidy here so we'll set it equal to zero.
replace maxsubRL_mean=0 if maxsubRL_mean<0

*30-year Treasury
gen PV_required_t30=monthlypayment_required*(1-A_t30^perm_term)/(1-A_t30)
replace PV_required_t30=monthlypayment_required*(1-A_t30^360)/(1-A_t30) if perm_term==.
gen maxsubRL_t30=RPA-PV_required_t30 if RL==1
replace maxsubRL_t30=0 if maxsubRL_t30<0

**************************
*Minimum subsidy - RL
**************************

gen monthlypayment_full=imputedannualpayment/12

*Mean Market Rate

gen PV_full_mean=monthlypayment_full*(1-A_mean^perm_term)/(1-A_mean)
replace PV_full_mean=monthlypayment_full*(1-A_mean^360)/(1-A_mean) if perm_term==.
gen minsubRL_mean=RPA-PV_full_mean if RL==1
replace minsubRL_mean=0 if minsubRL_mean<0

*30-year Treasury

gen PV_full_t30=monthlypayment_full*(1-A_t30^perm_term)/(1-A_t30)
replace PV_full_t30=monthlypayment_full*(1-A_t30^360)/(1-A_t30) if perm_term==.

gen minsubRL_t30=RPA-PV_full_t30 if RL==1
replace minsubRL_t30=0 if minsubRL_t30<0

replace minsubRL_mean=maxsubRL_mean if minsubRL_mean>maxsubRL_mean
replace minsubRL_t30=maxsubRL_t30 if minsubRL_t30>maxsubRL_t30
*There are 11 (mean) and 3 (t30) observations with the min>max. Five of them are in diffpctgroup=3, which means the annual service is greater than the imputed
*payment using the interest rate by more than 2%. The other ones are in diffpctgroup=1, so they are very close to each other, but the annual
*service is still slightly higher. In all these cases, I think it makes sense to set the max and min subsidy to be equal to each other
*at the lower value of the two.

********************************************
***Calculate the subsidy for Deferred Loans
**********************************************

*maximum subsidy - DL
*This is the same as the RLs, so I'll just reuse it.
gen maxsubDL_mean=RPA-PV_required_mean if DL==1
replace maxsubDL_mean=0 if maxsubDL_mean<0

gen maxsubDL_t30=RPA-PV_required_t30 if DL==1
replace maxsubDL_t30=0 if maxsubDL_t30<0


*minimum subsidy - DL
*If there are no annual payments, it's pretty simple (there is one with an annual payment of $1 and a interest rate equal to zero. I'm going to just assume that payment is zero.)

generate monthlyrate_app=perm_rate/12
gen DL_factor_mean=(1+monthlyrate_app)/(1+marketmonthlyrate_mean)
gen DL_factor_t30=(1+monthlyrate_app)/(1+marketmonthlyrate_t30)

gen minsubDL_mean=RPA*(1-DL_factor_mean^perm_term)-PV_required_mean if DL==1
replace minsubDL_mean=RPA*(1-DL_factor_mean^360)-PV_required_mean if DL==1&perm_term==.
replace minsubDL_mean=0 if minsubDL_mean<0

gen minsubDL_t30=RPA*(1-DL_factor_t30^perm_term)-PV_required_t30 if DL==1
replace minsubDL_t30=RPA*(1-DL_factor_t30^360)-PV_required_t30 if DL==1&perm_term==.
replace minsubDL_t30=0 if minsubDL_t30<0


******************************************
*Below market-rate loans
******************************************
**This is more complicated now that we incorporate the comparison between required and imputed payment.

gen maxsub_belowmarket_mean=RPA-PV_required_mean  if RL==0&DL==0&notloan==0&perm_rate<marketratemean&diffpctgroup==1
replace maxsub_belowmarket_mean=RPA-PV_required_mean  if RL==0&DL==0&notloan==0&perm_rate<marketratemean&diffpctgroup==2
replace maxsub_belowmarket_mean=RPA-PV_full_mean  if RL==0&DL==0&notloan==0&perm_rate<marketratemean&diffpctgroup==3

gen maxsub_belowmarket_t30=RPA-PV_required_t30  if RL==0&DL==0&notloan==0&perm_rate<treasury30&diffpctgroup==1
replace maxsub_belowmarket_t30=RPA-PV_required_t30  if RL==0&DL==0&notloan==0&perm_rate<treasury30&diffpctgroup==2
replace maxsub_belowmarket_t30=RPA-PV_full_t30  if RL==0&DL==0&notloan==0&perm_rate<treasury30&diffpctgroup==3

gen minsub_belowmarket_mean=RPA-PV_required_mean  if RL==0&DL==0&notloan==0&perm_rate<marketratemean&diffpctgroup==1
replace minsub_belowmarket_mean=RPA-PV_full_mean  if RL==0&DL==0&notloan==0&perm_rate<marketratemean&diffpctgroup==2
replace minsub_belowmarket_mean=RPA-PV_required_mean  if RL==0&DL==0&notloan==0&perm_rate<marketratemean&diffpctgroup==3

gen minsub_belowmarket_t30=RPA-PV_required_t30  if RL==0&DL==0&notloan==0&perm_rate<treasury30&diffpctgroup==1
replace minsub_belowmarket_t30=RPA-PV_full_t30  if RL==0&DL==0&notloan==0&perm_rate<treasury30&diffpctgroup==2
replace minsub_belowmarket_t30=RPA-PV_required_t30  if RL==0&DL==0&notloan==0&perm_rate<treasury30&diffpctgroup==3

replace maxsub_belowmarket_mean=0 if maxsub_belowmarket_mean<0
replace maxsub_belowmarket_t30=0 if maxsub_belowmarket_t30<0
replace minsub_belowmarket_mean=0 if minsub_belowmarket_mean<0
replace minsub_belowmarket_t30=0 if minsub_belowmarket_t30<0



******************************************
*Market-rate loans 
******************************************
*We set the subsidy equal to zero for all loans with interest rates greater than the market interest rate, so they are omitted here.

******************************************
**Donations**
******************************************
*The subsidy is the value minus any required payments. We only calculate this once.

gen maxsub_donation_mean=RPA-PV_required_mean if donations_othercontributions==1
replace maxsub_donation_mean=0 if maxsub_donation_mean<0 &donations_othercontributions==1
gen maxsub_donation_t30=RPA-PV_required_t30 if donations_othercontributions==1
replace maxsub_donation_t30=0 if maxsub_donation_t30<0 &donations_othercontributions==1

foreach x in DL RL donations_othercontributions  lowrateloanother_mean lowrateloanother_t30  {
    gen RPA_`x'=RPA if `x'==1
}

gen RPA_marketloan_mean=RPA if marketrateloanother_mean==1
gen RPA_marketloan_t30=RPA if marketrateloanother_t30==1

collapse (max) liunits (sum) RPA RPA_* min* max*,by(tcac nine constructiontype)
recode min* max* (.=0)
gen minsubRL_mean_unit=minsubRL_mean /liunits
gen minsubRL_t30_unit=minsubRL_t30 /liunits
gen maxsubRL_mean_unit=maxsubRL_mean /liunits
gen maxsubRL_t30_unit=maxsubRL_t30 /liunits
gen minsubDL_mean_unit=minsubDL_mean /liunits
gen minsubDL_t30_unit=minsubDL_t30 /liunits
gen maxsubDL_mean_unit=maxsubDL_mean /liunits
gen maxsubDL_t30_unit=maxsubDL_t30 /liunits
gen minsub_belowmarket_mean_unit=minsub_belowmarket_mean/liunits
gen minsub_belowmarket_t30_unit=minsub_belowmarket_t30/liunits
gen maxsub_belowmarket_mean_unit=maxsub_belowmarket_mean/liunits
gen maxsub_belowmarket_t30_unit=maxsub_belowmarket_t30/liunits
gen maxsub_donation_mean_unit=maxsub_donation_mean/liunits
gen maxsub_donation_t30_unit=maxsub_donation_t30/liunits



*Use this comment to create tables for four and nine percent credit projects separately
*keep if nineper==1
*keep if constructiontype=="A&R"



collapse (mean)  *_unit [fw=liunits]

format %16.0g *
